/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sai.transacciones;

import com.sai.conexion.PoolConexion;
import com.sai.sql.SqlIntegradora;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Leizar Zar
 */
public class TIntegradora {
    public static String Consultar() {
        Connection conex = (Connection) PoolConexion.getInstance().getConexion();
        String tablaAutores;

        try {
            PoolConexion.getInstance().commit(conex);
            PreparedStatement stm = null;
            ResultSet rs = null;

            stm = (PreparedStatement) conex.prepareStatement(SqlIntegradora.SELECT_ALL);
            rs = stm.executeQuery();

            tablaAutores = "<table align='center' border='1' color='red'>"
                + "<tr>"
                + "<th>Autor</th>"
                + "<th>Telefono</th>"
                + "<th>Correo Electronico</th>"
                + "<th>Estado</th>"
                + "</tr>";

            while (rs.next()) {
                tablaAutores += "<tr>"
                        + "<td>" + rs.getString(2) + "</td>"
                        + "<td>" + rs.getString(3) + "</td>"
                        + "<td>" + rs.getString(4) + "</td>"
                        + "<td>" + rs.getString(5) + "</td>"
                        + "</tr>";
            }
            tablaAutores += "</table>";
        } catch (SQLException ex) {
            return "No existe usuario.";
        } finally {
            PoolConexion.getInstance().cerrarConexion(conex);
        }
        return tablaAutores;
    }
    
    public static String Registrar(String nombre,String APaterno,String AMaterno, String Grupo, String Materia, String Matricula,String pass) {
        Connection conex = (Connection) PoolConexion.getInstance().getConexion();
        String resp = null;

        try{
            PoolConexion.getInstance().beginTransaction(conex);
            PoolConexion.getInstance().commit(conex);
            
            PreparedStatement stm = null;

            stm = (PreparedStatement) conex.prepareStatement(SqlIntegradora.INSERT);
            stm.setString(1, nombre);
            stm.setString(2, APaterno);
            stm.setString(3, AMaterno);
            stm.setString(4, Grupo);
            stm.setString(5, Materia);
            stm.setString(6, Matricula);     
            stm.executeUpdate();

            stm = null;
            stm = (PreparedStatement) conex.prepareStatement(SqlIntegradora.UPDATE_AFTER_UPDATE);
            stm.setString(2, pass);
            stm.executeUpdate();

            PoolConexion.getInstance().endTransaction(conex);
            resp = "OK";
        } catch (SQLException ex) {
            return "Error al registrar el Revisor." + ex;
        } finally {
            PoolConexion.getInstance().cerrarConexion(conex);
        }
        return resp;
    }
}
